import pandas as pd
import mysql.connector
from mysql.connector import Error
import os
import glob


def create_table(cursor, table_name, columns):
    """
    创建表格函数
    参数:
        cursor: MySQL游标对象
        table_name: 表名
        columns: 列定义
    """
    create_table_query = f"CREATE TABLE `{table_name}` ({columns})"
    print(f"创建表的SQL语句: {create_table_query}")  # 调试输出
    cursor.execute(create_table_query)
    print(f"创建表格 {table_name} 成功。")


def get_columns_definition(data):
    """
    获取列定义函数
    参数:
        data: 数据框
    返回:
        列定义字符串
    """
    column_definitions = []
    for col in data.columns:
        col_type = 'VARCHAR(255)'
        if col == 'id':  # 检测到列名为“id”时，设置为主键并自增
            col_type = 'INT AUTO_INCREMENT PRIMARY KEY'
        elif pd.api.types.is_integer_dtype(data[col]):
            col_type = 'INT'
        elif pd.api.types.is_float_dtype(data[col]):
            col_type = 'FLOAT'
        elif pd.api.types.is_datetime64_any_dtype(data[col]):
            col_type = 'DATETIME'
        elif pd.api.types.is_object_dtype(data[col]):
            try:
                if data[col].apply(lambda x: isinstance(x, (bytes, bytearray))).all():
                    col_type = 'LONGBLOB'
            except:
                pass
        column_definitions.append(f"`{col}` {col_type}")
    return ", ".join(column_definitions)


def insert_data(cursor, table_name, data):
    """
    插入数据函数
    参数:
        cursor: MySQL游标对象
        table_name: 表名
        data: 数据框
    """
    placeholders = ", ".join(['%s'] * len(data.columns))
    columns = ", ".join([f"`{col}`" for col in data.columns])
    insert_query = f"INSERT INTO `{table_name}` ({columns}) VALUES ({placeholders})"
    values = data.applymap(lambda x: x if isinstance(x, (bytes, bytearray)) else x).values.tolist()
    cursor.executemany(insert_query, values)
    print(f"已成功将数据插入到表 {table_name} 中。")


def mysql_upgrade(file_path, table_name):
    """
    升级MySQL数据库函数
    参数:
        file_path: xlsx文件路径
        table_name: 表名
    """
    data = pd.read_excel(file_path, sheet_name='Sheet1')

    try:
        with mysql.connector.connect(
                host='10.21.249.41',
                database='_system',
                user='123',
                password='123456'
        ) as connection:
            cursor = connection.cursor()

            cursor.execute(f"SHOW TABLES LIKE '{table_name}'")
            result = cursor.fetchone()

            columns = get_columns_definition(data)
            print(f"生成的列定义: {columns}")  # 调试输出

            if result:
                cursor.execute(f"TRUNCATE TABLE `{table_name}`")
                print(f"表 {table_name} 已存在，已删除所有数据。")
            else:
                create_table(cursor, table_name, columns)

            insert_data(cursor, table_name, data)
            connection.commit()
            print("数据已成功覆盖。")

    except Error as e:
        print("连接到MySQL时出错", e)


def get_table_name_from_file(file_path):
    """
    从文件路径获取表名函数
    参数:
        file_path: 文件路径
    返回:
        表名
    """
    return os.path.splitext(os.path.basename(file_path))[0]


def data_upgrade():
    data_directory = '../public/data/tables'

    for file_path in glob.glob(os.path.join(data_directory, '*.xlsx')):
        table_name = get_table_name_from_file(file_path)
        print(f"处理文件: {file_path}, 表名: {table_name}")
        mysql_upgrade(file_path, table_name)


if __name__ == '__main__':
    data_upgrade()
